package com.elric.mall.common.utils;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.elric.mall.common.annotation.ImportExcelAnnotation;
import com.elric.mall.common.domain.DynamicExcelHeader;
import com.elric.mall.common.domain.ImportBase;
import com.elric.mall.common.enums.ExcelDataType;
import com.elric.mall.common.exception.ApiException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;
import java.util.stream.Collectors;


/**
 * excel导出工具类
 * @author 作者[Yuxuan.he]
 * @version 1.0.00  2021-10-12 10:32
 */
public class ExportExcelUtil {

    public static final int maxRowNum = 65000;// 为了防止excel超界，每页插入65000行数据

    public static final int WIDTH = 256;
    public static final short HEIGHT = 600;

    public static final String DEFAULT_TIME_FORMAT = "yyyy-MM-dd HH:mm:ss";

    /**
     * 设置表头
     * @param wb
     * @param sheetName
     * @param freezePane
     * @param object
     * @param rowNum
     * @throws Exception
     */
    public static HSSFSheet createHead(HSSFWorkbook wb,String sheetName,boolean freezePane,Object object,int rowNum,HorizontalAlignment align)throws Exception{
        HSSFSheet sheet = newSheet(wb, sheetName + 0);
        if(freezePane){
            //固定表头
            sheet.createFreezePane(0,1,0,1);
        }
        HSSFRow row = newRow(sheet, rowNum);
        row.setHeight(HEIGHT);
        // 获取实体所有属性
        Field[] fields = object.getClass().getDeclaredFields();
        // 列索引
        int index = 0;
        // 列名称
        String name = "";
        ImportExcelAnnotation importExcelAnnotation;
        //设置样式
        HSSFCellStyle hssfCellStyle = wb.createCellStyle();
        // 创建表头
        for (Field f : fields) {
            // 是否是注解
            if (f.isAnnotationPresent(ImportExcelAnnotation.class)) {
                // 获取注解
                importExcelAnnotation = f.getAnnotation(ImportExcelAnnotation.class);
                // 获取列索引
                index = importExcelAnnotation.columnIndex();
                // 列名称
                name = importExcelAnnotation.columnName();
                //宽度
                int columnWidth=importExcelAnnotation.columnWidth();
                // 创建单元格
                HSSFCell cell = newCell(row, (short) index);
                setCell(cell, name);
                sheet.setColumnWidth(index, WIDTH * columnWidth);
                sheet.setDefaultRowHeight(HEIGHT);
                //设置表头样式
                setStyle(hssfCellStyle, wb,cell,align);
            }
        }
        return sheet;
    }

    private static <T extends ImportBase> void createExtraTitle(List<T> data, XSSFSheet sheet, XSSFWorkbook wb) throws Exception {
        // 过滤类型为"表头"的数据并格式化
        List<T> headers = data.stream().filter(d -> StrUtil.equals(d.getDataType(), ExcelDataType.header.name()))
                .collect(Collectors.toList());
        if (!CollectionUtils.isEmpty(headers)){
            // 获取实体所有属性
            Field[] fields = data.get(0).getClass().getDeclaredFields();
            ExcelStyleHandler styleHandler = new ExcelStyleHandler(wb);
            int rowNum = 0;
            //数据body部分
            for (T obj : headers) {
                //插入一行
                XSSFRow row = newRow(sheet, rowNum);
                if (obj.isRegion()) {
                    //指定合并开始行、合并结束行 合并开始列、合并结束列
                    CellRangeAddress rangeAddress = new CellRangeAddress(rowNum, rowNum, obj.getStartNum(), obj.getEndNum());
                    //添加要合并地址到表格
                    sheet.addMergedRegion(rangeAddress);
                }
                for (Field f : fields) {
                    setsetCellForData(styleHandler, f, row, obj, false, wb, HorizontalAlignment.CENTER);
                }
                rowNum++;
            }
        }
    }

    /**
     * 设置表头
     * @param wb
     * @param sheetName
     * @param freezePane
     * @param object
     * @throws Exception
     */
    public static  <T extends ImportBase> XSSFSheet createHead(XSSFWorkbook wb, String sheetName, boolean freezePane, T object, List<T> data, HorizontalAlignment align)throws Exception{
        XSSFSheet sheet = newSheet(wb,sheetName + 0);
        createExtraTitle(data, sheet, wb);
        int rowNum = sheet.getLastRowNum()==0?0: sheet.getLastRowNum() + 1;
        XSSFRow row = newRow(sheet, rowNum);
        row.setHeight((short)(HEIGHT * 1.5));
        // 获取实体所有属性
        Field[] fields = object.getClass().getDeclaredFields();
        // 列索引
        int index = 0;
        // 列名称
        String name = "";
        ImportExcelAnnotation importExcelAnnotation;
        ExcelStyleHandler styleHandler = new ExcelStyleHandler(wb);
        // 创建表头
        for (Field f : fields) {
            // 是否是注解
            if (f.isAnnotationPresent(ImportExcelAnnotation.class)) {
                // 获取注解
                importExcelAnnotation = f.getAnnotation(ImportExcelAnnotation.class);
                // 获取列索引
                index = importExcelAnnotation.columnIndex();
                // 列名称
                name = importExcelAnnotation.columnName();
                //宽度
                int columnWidth=importExcelAnnotation.columnWidth();
                // 创建单元格
                XSSFCell cell = newCell(row, (short) index);
                setCell(cell, name);
                sheet.setColumnWidth(index, WIDTH * columnWidth);
                sheet.setDefaultRowHeight(HEIGHT);
                //设置表头样式
                cell.setCellStyle(styleHandler.getDefaultStyle());
            }
        }
        if(freezePane){
            //固定表头
            sheet.createFreezePane(0,sheet.getLastRowNum() + 1,0,1);
        }
        return sheet;
    }

    /**
     * 根据传入的动态表格信息设置表头
     * @param wb
     * @param sheetName
     * @param freezePane
     * @param dynamicExcelHeader
     * @param rowNum
     * @param align
     * @return
     * @throws Exception
     */
    public static XSSFSheet createHead(XSSFWorkbook wb, String sheetName, boolean freezePane, List<DynamicExcelHeader> dynamicExcelHeader, int rowNum, HorizontalAlignment align) throws Exception{
        XSSFSheet sheet = newSheet(wb,sheetName + 0);
        if(freezePane){
            //固定表头
            sheet.createFreezePane(0,1,0,1);
        }
        XSSFRow row = newRow(sheet, rowNum);
        row.setHeight(HEIGHT);
        // 列名称
        String name = "";
        ExcelStyleHandler styleHandler = new ExcelStyleHandler(wb);
        // 创建表头
        if (!CollectionUtils.isEmpty(dynamicExcelHeader)){
            for (int i = 0; i < dynamicExcelHeader.size(); i++) {
                // 创建单元格
                XSSFCell cell = newCell(row, (short) i);
                setCell(cell, dynamicExcelHeader.get(i).getColumnName());
                int columnWidth= dynamicExcelHeader.get(i).getColumnWidth();
                sheet.setColumnWidth(i, WIDTH * columnWidth);
                sheet.setDefaultRowHeight(HEIGHT);
                //设置表头样式
                cell.setCellStyle(styleHandler.getDefaultStyle());
            }
        }
        return sheet;
    }

    /**
     * 把数据导出为excel
     * @param data 数据
     * @param sheetName sheet名称
     * @param freezePane 是否固定表头
     * @return
     * @throws Exception
     */
    public static HSSFWorkbook excelWrite(List<?> data,String sheetName,boolean freezePane,Object object) throws Exception{
        HSSFWorkbook wb = newWorkbook();
        if(data.size()>=maxRowNum){
            throw new ApiException("数据量过多，请输入查询条件后在导出！");
        }
        int rowNum = 0;
        //创建表头
        HSSFSheet sheet = createHead(wb,sheetName,freezePane,object,rowNum,HorizontalAlignment.CENTER);
        rowNum++;
        if(!CollectionUtils.isEmpty(data)){
            //body部分
            setHSSFWorkbook(data,sheet,rowNum,false,wb,HorizontalAlignment.LEFT);
        }
        return wb;
    }

    /**
     * 把数据导出为excel xlsx
     * @param data 数据
     * @param sheetName sheet名称
     * @param freezePane 是否固定表头
     * @return
     * @throws Exception
     */
    public static <T extends ImportBase> XSSFWorkbook excelWriteWithXlsx(List<T> data, String sheetName, boolean freezePane, T object) throws Exception{
        XSSFWorkbook wb = newWorkbookWithXlsx();
        if(data.size() >= maxRowNum){
            throw new ApiException("数据量过多，请输入查询条件后在导出！");
        }
        //创建表头
        XSSFSheet sheet = createHead(wb, sheetName, freezePane, object, data, HorizontalAlignment.CENTER);
        int bodyRowNum = sheet.getLastRowNum() + 1;
        if(!CollectionUtils.isEmpty(data)){
            List<T> bodys = data.stream().filter(d -> StrUtil.equals(d.getDataType(), ExcelDataType.body.name()))
                    .collect(Collectors.toList());
            //body部分
            setXSSFWorkbook(bodys, sheet, bodyRowNum,false, wb, HorizontalAlignment.LEFT);
        }
        return wb;
    }

    public static XSSFWorkbook excelWriteWithXlsx(List<DynamicExcelHeader> dynamicExcelHeader, List<Object> data, boolean freezePane) throws Exception {
        XSSFWorkbook wb = newWorkbookWithXlsx();
        if(data.size() >= maxRowNum){
            throw new ApiException("数据量过多，请输入查询条件后在导出！");
        }
        int rowNum = 0;
        //创建表头
        XSSFSheet sheet = createHead(wb,"sheet",freezePane, dynamicExcelHeader,rowNum,HorizontalAlignment.CENTER);
        rowNum++;
        if(!CollectionUtils.isEmpty(data)){
            //body部分
            setXSSFWorkbook(data, dynamicExcelHeader, sheet, rowNum, wb, HorizontalAlignment.LEFT);
        }
        return wb;
    }

    /**
     * 设置excel主体部分
     * @param data
     * @param sheet
     * @param num
     * @throws Exception
     */
    public static int setHSSFWorkbook(List<?> data,HSSFSheet sheet,int num,boolean bold,HSSFWorkbook wb,HorizontalAlignment align)throws Exception{
        // 获取实体所有属性
        Field[] fields = data.get(0).getClass().getDeclaredFields();
        int rowNum=num;
        HSSFCellStyle hssfCellStyle = wb.createCellStyle();
        //数据body部分
        for (Object obj : data) {
            //插入一行
            HSSFRow row = newRow(sheet, rowNum);
            for (Field f : fields) {
                setsetCellForData(hssfCellStyle, f,row,obj,bold,wb,align);
            }
            rowNum++;
        }
        return rowNum;
    }

    /**
     * 设置excel主体部分 xlsx
     * @param data
     * @param sheet
     * @param num
     * @throws Exception
     */
    public static <T extends ImportBase> int setXSSFWorkbook(List<T> data,XSSFSheet sheet,int num,boolean bold,XSSFWorkbook wb,HorizontalAlignment align)throws Exception{
        // 获取实体所有属性
        Field[] fields = data.get(0).getClass().getDeclaredFields();
        ExcelStyleHandler handler = new ExcelStyleHandler(wb);
        int rowNum=num;
        //数据body部分
        for (T obj : data) {
            //插入一行
            XSSFRow row = newRow(sheet, rowNum);
            if (obj.isRegion()) {
                //指定合并开始行、合并结束行 合并开始列、合并结束列
                CellRangeAddress rangeAddress = new CellRangeAddress(rowNum, rowNum, obj.getStartNum(), obj.getEndNum());
                //添加要合并地址到表格
                sheet.addMergedRegion(rangeAddress);
            }
            for (Field f : fields) {
                setsetCellForData(handler, f, row, obj, bold, wb, align);
            }
            rowNum++;
        }
        return rowNum;
    }

    /**
     * 根据动态表头写入excel body
     * @param data
     * @param dynamicExcelHeaders
     * @param sheet
     * @param num
     * @param wb
     * @param align
     * @return
     * @throws Exception
     */
    public static int setXSSFWorkbook(List<?> data, List<DynamicExcelHeader> dynamicExcelHeaders, XSSFSheet sheet, int num, XSSFWorkbook wb, HorizontalAlignment align)throws Exception{
        int rowNum=num;
        ExcelStyleHandler handler = new ExcelStyleHandler(wb);
        //数据body部分
        for (Object obj : data) {
            JSONObject jsonObject = JSONUtil.parseObj(obj);
            //插入一行
            XSSFRow row = newRow(sheet, rowNum);
            row.setHeight(HEIGHT);
            for (int i = 0; i < dynamicExcelHeaders.size(); i++) {
                // 创建单元格
                XSSFCell cell = newCell(row, (short) i);
                String value = jsonObject.get(dynamicExcelHeaders.get(i).getColumnCode()).toString();
                setCell(cell,value);
                cell.setCellStyle(handler.getDefaultStyle());
            }
            rowNum++;
        }
        return rowNum;
    }

    /**
     * 设置值
     * @param f
     * @param row
     * @param data
     * @throws Exception
     */
    public static void setsetCellForData(HSSFCellStyle hssfCellStyle, Field f,HSSFRow row,Object data,boolean bold,HSSFWorkbook wb,HorizontalAlignment align)throws Exception{
        ImportExcelAnnotation importExcelAnnotation=null;
        row.setHeight(HEIGHT);
        // 列索引
        int index = 0;
        // 设置属性可访问
        f.setAccessible(true);
        // 判断是否是注解
        if (f.isAnnotationPresent(ImportExcelAnnotation.class)) {
            // 获取注解
            importExcelAnnotation = f.getAnnotation(ImportExcelAnnotation.class);
            // 获取列索引
            index = importExcelAnnotation.columnIndex();
            // 是否需要转换
            boolean columnConvert=importExcelAnnotation.columnConvert();
            // 时间格式化
            String dateFormat=importExcelAnnotation.dateFormat();
            // 金额去0
            boolean stripTrailingZeros=importExcelAnnotation.stripTrailingZeros();
            // 创建单元格
            HSSFCell cell = newCell(row, (short) index);
            Object value=ObjectCreateUtil.getValueForField(data,f.getName());
            String fieldType = f.getType().getSimpleName();
            if ("Date".equals(fieldType)) {
                if(!StringUtils.isEmpty(value)){
                    String format= DEFAULT_TIME_FORMAT;
                    if(!StringUtils.isEmpty(dateFormat)){
                        format=dateFormat;
                    }
                    value=DateUtils.formatDate((Date)value,format);
                }
            }
            if(value != null&&("BigDecimal".equals(fieldType) || "Integer".equals(fieldType) || "Double".equals(fieldType) || "Long".equals(fieldType))) {
                DecimalFormat df;
                if(StringUtils.isEmpty(dateFormat) || Objects.equals("",dateFormat)) {
                    df = new DecimalFormat("0");
                }   else {
                    df = new DecimalFormat(dateFormat);
                }
                value = df.format(value);
            }
            if(columnConvert){
                StringBuilder getConvertMethodName = new StringBuilder("get");
                getConvertMethodName.append(f.getName().substring(0, 1).toUpperCase())
                        .append(f.getName().substring(1))
                        .append("Convert");
                Method getConvertMethod = data.getClass().getMethod(getConvertMethodName.toString(), new Class[]{});
                value=getConvertMethod.invoke(data);
            }
            if(stripTrailingZeros){
                try {
                    if(!StringUtils.isEmpty(value)){
                        BigDecimal res=(BigDecimal)value;
                        String valueBefore = res.stripTrailingZeros().toPlainString();
                        BigDecimal valueLast = new BigDecimal(valueBefore);
                        value = valueLast;
                    }
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            setCell(cell,value);
            setBodyStyle(hssfCellStyle, wb,cell,align);

            if(bold){
                setStyle(hssfCellStyle, wb,cell,align);
            }
        }
    }

    /**
     * 设置值
     * @param handler
     * @param f
     * @param row
     * @param data
     * @param bold
     * @param wb
     * @param align
     * @param <T>
     * @throws Exception
     */
    public static <T extends ImportBase> void setsetCellForData(ExcelStyleHandler handler, Field f,XSSFRow row,T data,boolean bold,XSSFWorkbook wb,HorizontalAlignment align)throws Exception{
        ImportExcelAnnotation importExcelAnnotation = null;
        row.setHeight(data.getCellHeight());
        // 列索引
        int index = 0;
        // 设置属性可访问
        f.setAccessible(true);
        ImportBase styleObj = new ImportBase();
        BeanUtil.copyProperties(data, styleObj);
        // 判断是否是注解
        if (f.isAnnotationPresent(ImportExcelAnnotation.class)) {
            // 获取注解
            importExcelAnnotation = f.getAnnotation(ImportExcelAnnotation.class);
            // 获取列索引
            index = importExcelAnnotation.columnIndex();
            // 是否需要转换
            boolean columnConvert=importExcelAnnotation.columnConvert();
            //时间格式化
            String dateFormat=importExcelAnnotation.dateFormat();
            // 水平对齐格式
            HorizontalAlignment alignment = importExcelAnnotation.alignment();
            // 单独设置水平对齐方式优先级高于注解
            if (Objects.isNull(data.getAlignment())) {
                styleObj.setAlignment(alignment);
            }
            //数字格式化
            String numberFormat=importExcelAnnotation.numberFormat();
            //金额去0
            boolean stripTrailingZeros=importExcelAnnotation.stripTrailingZeros();
            // 创建单元格
            XSSFCell cell = newCell(row, (short) index);
            Object value=ObjectCreateUtil.getValueForField(data,f.getName());
            String fieldType = f.getType().getSimpleName();
            if ("Date".equals(fieldType)) {
                if(!StringUtils.isEmpty(value)){
                    String format = DEFAULT_TIME_FORMAT;
                    if(!StringUtils.isEmpty(dateFormat)){
                        format = dateFormat;
                    }
                    value=DateUtils.formatDate((Date)value,format);
                }
            }
            if(!StringUtils.isEmpty(numberFormat) && Objects.nonNull(value)) {
                if("BigDecimal".equals(fieldType) || "Integer".equals(fieldType) || "Double".equals(fieldType) || "Long".equals(fieldType)) {
                    DecimalFormat df;
                    if(StringUtils.isEmpty(dateFormat) || Objects.equals("",dateFormat)) {
                        df = new DecimalFormat("0");
                    }   else {
                        df = new DecimalFormat(dateFormat);
                    }
                    value = df.format(value);
                }
            }
            if(columnConvert){
                StringBuilder getConvertMethodName = new StringBuilder("get");
                getConvertMethodName.append(f.getName().substring(0, 1).toUpperCase())
                        .append(f.getName().substring(1))
                        .append("Convert");
                Method getConvertMethod = data.getClass().getMethod(getConvertMethodName.toString(), new Class[]{});
                value=getConvertMethod.invoke(data);
            }
            if(stripTrailingZeros){
                try {
                    if(!StringUtils.isEmpty(value)){
                        BigDecimal res=(BigDecimal)value;
                        String valueBefore = res.stripTrailingZeros().toPlainString();
                        BigDecimal valueLast = new BigDecimal(valueBefore);
                        value = valueLast;
                    }
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            setCell(cell,value);
            cell.setCellStyle(handler.getStyle(styleObj));
        }
    }

    /**
     * 设置样式
     * @param wb
     * @param cell
     * @param align
     */
    public static void setStyle(HSSFCellStyle hssfCellStyle, HSSFWorkbook wb,HSSFCell cell,HorizontalAlignment align){
        //居中样式
        hssfCellStyle.setAlignment(align);
        hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体
        HSSFFont font = wb.createFont();
        //粗体显示
        font.setBold(true);
        hssfCellStyle.setFont(font);
        cell.setCellStyle(hssfCellStyle);
    }

    /**
     * 设置body样式
     * @param wb
     * @param cell
     * @param align
     */
    public static void setBodyStyle(HSSFCellStyle hssfCellStyle, HSSFWorkbook wb,HSSFCell cell,HorizontalAlignment align){
        //居中样式
        hssfCellStyle.setAlignment(align);
        hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体
        HSSFFont font = wb.createFont();
        hssfCellStyle.setFont(font);
        cell.setCellStyle(hssfCellStyle);
    }

    public static HSSFWorkbook newWorkbook() {
        HSSFWorkbook wb = new HSSFWorkbook();
        return wb;
    }

    public static HSSFSheet newSheet(HSSFWorkbook wb, String sheetName) {
        HSSFSheet sheet = wb.createSheet(sheetName);
        return sheet;
    }

    public static HSSFRow newRow(HSSFSheet sheet, int rowNum) {
        HSSFRow row = sheet.createRow(rowNum);
        return row;
    }

    public static HSSFCell newCell(HSSFRow row, short cellNum) {
        HSSFCell cell = row.createCell(cellNum);
        return cell;
    }

    public static void setCell(HSSFCell cell, Object cellValue) {
        if (cellValue == null) {
            HSSFRichTextString hrts = new HSSFRichTextString("");
            cell.setCellValue(hrts);
        } else if (cellValue instanceof String) {
            HSSFRichTextString hrts = new HSSFRichTextString((String) cellValue);
            cell.setCellValue(hrts);
        } else if (cellValue instanceof Boolean) {
            cell.setCellValue((Boolean) cellValue);
        } else if (cellValue instanceof Calendar) {
            cell.setCellValue((Calendar) cellValue);
        } else if (cellValue instanceof Date) {
            cell.setCellValue((Date) cellValue);
        } else if (cellValue instanceof Double) {
            cell.setCellValue((Double) cellValue);
        } else if (cellValue instanceof Integer) {
            cell.setCellValue((Integer) cellValue);
        } else if (cellValue instanceof Float) {
            cell.setCellValue((Float) cellValue);
        } else if (cellValue instanceof BigDecimal) {
            Float resCellValue = ((BigDecimal) cellValue).floatValue();
            cell.setCellValue((Float) resCellValue);
        } else {
            HSSFRichTextString hrts = new HSSFRichTextString(cellValue
                    .toString());
            cell.setCellValue(hrts);
        }
    }

    public static XSSFWorkbook newWorkbookWithXlsx() {
        XSSFWorkbook wb = new XSSFWorkbook();
        return wb;
    }

    public static XSSFSheet newSheet(XSSFWorkbook wb, String sheetName) {
        XSSFSheet sheet = wb.createSheet(sheetName);
        return sheet;
    }

    public static XSSFRow newRow(XSSFSheet sheet, int rowNum) {
        XSSFRow row = sheet.createRow(rowNum);
        return row;
    }

    public static XSSFCell newCell(XSSFRow row, short cellNum) {
        XSSFCell cell = row.createCell(cellNum);
        return cell;
    }

    public static void setCell(XSSFCell cell, Object cellValue) {
        if (cellValue == null) {
            XSSFRichTextString hrts = new XSSFRichTextString("");
            cell.setCellValue(hrts);
        } else if (cellValue instanceof String) {
            XSSFRichTextString hrts = new XSSFRichTextString((String) cellValue);
            cell.setCellValue(hrts);
        } else if (cellValue instanceof Boolean) {
            cell.setCellValue((Boolean) cellValue);
        } else if (cellValue instanceof Calendar) {
            cell.setCellValue((Calendar) cellValue);
        } else if (cellValue instanceof Date) {
            cell.setCellValue((Date) cellValue);
        } else if (cellValue instanceof Double) {
            cell.setCellValue((Double) cellValue);
        } else if (cellValue instanceof Integer) {
            cell.setCellValue((Integer) cellValue);
        } else if (cellValue instanceof Float) {
            cell.setCellValue((Float) cellValue);
        }
        else if (cellValue instanceof BigDecimal) {
            Double resCellValue = ((BigDecimal) cellValue).doubleValue();
            cell.setCellValue((Double) resCellValue);
        }
        else {
            XSSFRichTextString hrts = new XSSFRichTextString(cellValue
                    .toString());
            cell.setCellValue(hrts);
        }
    }

    public HSSFCellStyle setDataFormat(HSSFCellStyle cellStyle) {
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        return cellStyle;
    }
}
